using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace QLHXNK.DataAccess
{
    class ExspencesData
    {
        #region---------------------------------------------Thêm, xoá, sửa-----------------------------------------

        public static int CreateExspences(String expensesCode, String expensesName, String remark)
        {
            try
            {
                string sqlstring = "insert into Expenses(expenses_code,expenses_name,remark) values('" + expensesCode.Replace("'", "''") + "','"
                                    + expensesName.Replace("'", "''") + "','" + remark.Replace("'", "''") + "')";
                ConnectionData connectionData = new ConnectionData();
                if (connectionData.Command(sqlstring) != 0)
                    return Int32.Parse(GetExpenses(expensesCode,-1).Rows[0]["ID"].ToString());
                return 0;
            }
            catch
            {
                return 0;
            }
        }


        public static int DeleteExpenses(int expensesID)
        {
            try
            {
                string sqlstring = "delete from Expenses where ID=" + expensesID + "";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.Command(sqlstring);
            }
            catch
            {
                return 0;
            }
        }


        public static int UpdateExpenses(int expensesID, String expensesCode, String expensesName,String remark)
        {
            try
            {
                string sqlstring = "update Expenses set expenses_code='" + expensesCode.Replace("'", "''")
                                    + "',expenses_name='" + expensesName.Replace("'", "''")
                                    + "', remark='" + remark.Replace("'", "''") + "' where ID=" + expensesID + "";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.Command(sqlstring);
            }
            catch
            {
                return 0;
            }
        }

        #endregion

        #region ------------------------------------------Lấy thông tin item---------------------------------------

        public static DataTable GetExpenses(String expensesCode, int ID)
        {
            try
            {
                string queryCondition = "";
                if (ID != -1)
                    queryCondition += " ID = " + ID + " ";
                if (expensesCode != "")
                    queryCondition += " expenses_code = '" + expensesCode.Replace("'", "''") + "' ";
                string sqlstring = "select * from Expenses ";
                if (queryCondition != "")
                    sqlstring += " where " + queryCondition;
                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch
            {
                return null;
            }
        }

        public static DataTable GetExpensesInMonthlyExpenses(int ID)
        {
            try
            {
                string sqlstring = "select * from Monthly_Expenses where  expenses_id="+ID+"";
                ConnectionData connectionData = new ConnectionData();
                return connectionData.List(sqlstring);
            }
            catch
            {
                return null;
            }
        }
        #endregion

    }
}
